package com.srv.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.srv.common.DataSourceManager;
import com.srv.common.IDField;
import com.srv.model.ID;
import com.srv.persistence.IIDPersistence;
import com.srv.persistence.PersistenceException;
import com.srv.util.DataBaseUtil;
import com.srv.util.ObjectField;
import com.srv.util.SqlUtil;

public class DBIDPersistence implements IIDPersistence {

	private static Log log = LogFactory.getLog(DBIDPersistence.class);
	private static final String INSERT_SUSER_SQL;	
	private static final String DEFAULT_DATASOURCE_NAME = "srv_def";	
	private static final String KEY_TABLE_NAME_SUSER = "PID_ID_MAPPING";
	static{
		StringBuilder builder = new StringBuilder();
		builder.append("INSERT INTO PID_ID_MAPPING ");
		builder.append("(PID,ID,TYPE,CREATE_TIME, UPDATE_TIME,EXTEND_1,EXTEND_2)");
		builder.append(" VALUES ");
		builder.append("(?,?,?,?,?,?,?)");
		INSERT_SUSER_SQL = builder.toString();
	};
	
	public ID getID(String id) throws PersistenceException{
		Map<ObjectField, Object> keyValues = new HashMap<ObjectField, Object>();
		keyValues.put(IDField.ID, id);
		List<ID> list = queryID(keyValues);
		
		if(list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	public List<ID> queryID(Map<ObjectField, Object> keyValues) throws PersistenceException{
		String sql = getSelectSql(keyValues);
		log.debug("The ID query sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List<ID> list = new ArrayList<ID>();
		try {
			conn = DataSourceManager.get().getDataSource(DEFAULT_DATASOURCE_NAME).getConnection();
			st = conn.prepareStatement(sql);
			SqlUtil.setStmtParams(st, 1, keyValues);
			rs = st.executeQuery();
			
			while(rs.next()){
				list.add(rsToObject(rs));
			}
		} catch (SQLException e) {
			log.error("query SummaryUser fail");
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeResultSet(rs);
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		return list;
	}
	public ID insertID(ID idObj) throws PersistenceException {
		if(idObj==null){return null;}
		if(idObj.type<0){
			throw new PersistenceException("ID's type is not null");
		}

		if(idObj.createTime == null){
			idObj.createTime = new Date();
		}
		if(idObj.updateTime == null){
			idObj.updateTime = new Date();
		}
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = DataSourceManager.get().getDataSource(DEFAULT_DATASOURCE_NAME).getConnection();
//			conn.setAutoCommit(false);
			st = conn.prepareStatement(INSERT_SUSER_SQL);
			log.debug(INSERT_SUSER_SQL);
			//PID,ID,TYPE,CREATE_TIME, UPDATE_TIME
			st.setLong(1, idObj.pid);
			st.setString(2, idObj.id);
			st.setInt(3, idObj.type);			
			st.setTimestamp(4, new Timestamp(idObj.createTime.getTime()));
			st.setTimestamp(5, new Timestamp(idObj.updateTime.getTime()));
			st.setString(6, idObj.extend_1);
			st.setString(7, idObj.extend_2);
			int flag = st.executeUpdate();
			if(flag <= 0){
				throw new PersistenceException("insert id fail");
			}			
			return idObj;
		} catch (SQLException e) {
			log.error("insert ID fail");
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeResultSet(rs);
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
	}

	public boolean updateID(String id, Map<ObjectField, Object> keyValues)throws PersistenceException {
		String sql = getUpdateSql(keyValues);
		log.debug("The ID update Sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(DEFAULT_DATASOURCE_NAME).getConnection();
			st = conn.prepareStatement(sql);
			int idx = SqlUtil.setStmtParams(st, 1, keyValues);
			st.setString(idx,id);
			
			return st.executeUpdate() > 0;
		} catch (SQLException e) {
			log.error("update ID fail");
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
	}
	private String getSelectSql(Map<ObjectField, Object> keyValues){
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT * FROM ").append(KEY_TABLE_NAME_SUSER);
		
		if (keyValues != null && keyValues.size() > 0) {
			builder.append(" WHERE ").append(SqlUtil.generateWhereCause(keyValues));
        }
		
        return builder.toString();
	}
	
	private String getUpdateSql(Map<ObjectField, Object> keyValues) {
		StringBuilder strBuf = new StringBuilder();

		strBuf.append("UPDATE ").append(KEY_TABLE_NAME_SUSER);

		if (keyValues != null && keyValues.size() > 0) {
			strBuf.append(" SET ").append(SqlUtil.generateSetCause(keyValues));
		}

		strBuf.append(" WHERE ID = ?");

		return strBuf.toString();
	}
	
	private ID rsToObject(ResultSet rs) throws SQLException{
		ID idObj = new ID();
		idObj.pid = rs.getLong("PID");
		idObj.id = rs.getString("ID");
		idObj.type = rs.getInt("TYPE");
		idObj.createTime = new Date(rs.getTimestamp("CREATE_TIME").getTime());		
		idObj.updateTime =  new Date(rs.getTimestamp("UPDATE_TIME").getTime());	
		idObj.extend_1 = rs.getString("EXTEND_1");
		idObj.extend_2 = rs.getString("EXTEND_2");
		return idObj;
	}
}
